package com.xxl.job.executor.service.impl;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IMonitorInfoStatisService;

@Service("MonitorInfoStatisService")
public class MonitorInfoStatisService implements IMonitorInfoStatisService {
    Connection connPre = null;

    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作===");

        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);
            String recordStr = DBUtils.getCurDate(connPre);
            String syncEndDate = recordStr + " 00:00:00";  
            String syncStartDate = DBUtils.getTenDayAgoData(connPre,syncEndDate);           
            syncEndDate =  recordStr + " 23:59:59";
         
            ///清理需要修改的数据
            clearNum(syncStartDate, syncEndDate, connPre) ;
           //统计站上传省中心etc出口数据           
            monitorMainEtcboutlistAll( syncStartDate, syncEndDate, connPre) ;
            //etc出口流水成功量
            monitorMainEtcboutlistAllSucc( syncStartDate, syncEndDate, connPre) ;
            //etc出口流水待治理量
            monitorMainEtcboutlistAllNotdeal( syncStartDate, syncEndDate, connPre) ;
            // etc出口流水拦截量
            monitorMainEtcboutlistIntecptcn(syncStartDate,syncEndDate,connPre);              
             // etc出口流水无需治理量
            monitorMainEtcboutlistNodoCnt(syncStartDate,syncEndDate,connPre);
            //统计站上传落地省中心出口其它交易量
            monitorMainBoutlistAll( syncStartDate, syncEndDate, connPre) ;
            //统计站上传落地省中心mtc出口其它成功交易量
            monitorMainBoutlistAllSucc( syncStartDate, syncEndDate, connPre) ;
            //统计站上传落地省中心mtc出口其它待治理交易量
            monitorMainBoutlistAllNotdeal( syncStartDate, syncEndDate, connPre) ;
            // mtc出口流水拦截量
            monitorMainMtcboutlistIntecptcn(syncStartDate, syncEndDate, connPre) ;            
            // mtc出口流水无需治理量           
            monitorMainMtcboutlistNodoCnt(syncStartDate, syncEndDate, connPre) ;
            //统计站上传落地省中心入口ETC交易量
            monitorMainEtcBinlistAll( syncStartDate, syncEndDate, connPre) ;
            // 统计站上传落地省中心入口ETC交易量成功
            monitorMainEtcBinlistAllSucc( syncStartDate, syncEndDate, connPre) ;
            //统计站上传落地省中心入口ETC待治理交易量
            monitorMainEtcBinlistAllNotdeal( syncStartDate, syncEndDate, connPre) ;
            //统计站上传落地省中心入口其它交易量
            monitorMainBinlistAll( syncStartDate, syncEndDate, connPre) ;
            //统计站上传落地省中心mtc入口其它成功交易量
            monitorMainBinlistAllSucc( syncStartDate, syncEndDate, connPre) ;
            //统计站上传落地省中心mtc入口其它待治理交易量
            monitorMainBinlistAllNotdeal( syncStartDate, syncEndDate, connPre) ;
          
         
            
            connPre.commit();
            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
    
    /**
     * 清理需要修改的数据
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void clearNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " update TB_STATIS_MONITOR_MAIN set STATION_TOTALCNT=0,STATION_SEND_CNT=0,NOTDEAL_CNT=0,INTECPT_CNT=0,NO_DO_CNT=0" +
    			"     where  STATIS_TYPE in ('1','2','3','4','5') " + 
    			"      and to_date(STATIS_DATE,'yyyy-mm-dd')>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" + 
    			"      and  to_date(STATIS_DATE,'yyyy-mm-dd')<TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" ;
     	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]清理需要修改的数据数据(条):" + count);	
    }
    
    /**
     * 统计站上传省中心etc出口数据
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainEtcboutlistAll(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"   using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'1' as STATIS_TYPE, count(1) STATION_TOTALCNT\r\n" + 
    			"      from etcboutlist_all e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_TOTALCNT = b.STATION_TOTALCNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_TOTALCNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_TOTALCNT,b.STATIS_TYPE,sysdate,sysdate)  ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]站上传落地省中心ect出口交易etcboutlist_all数据(条):" + count);	
    }
    
    
    /**
     * etc出口流水成功量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void monitorMainEtcboutlistAllSucc(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'1' as STATIS_TYPE, count(1) STATION_SEND_CNT\r\n" + 
    			"      from etcboutlist e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') " + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') " + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'      \r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd') \r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_SEND_CNT = b.STATION_SEND_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_SEND_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_SEND_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]etcboutlist站上传落地省中心ect出口交易成功量数据(条):" + count);	
    }
    
    /**
     * etc出口流水待治理量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void monitorMainEtcboutlistAllNotdeal(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'1' as STATIS_TYPE, count(1) NOTDEAL_CNT\r\n" + 
    			"      from etcboutlist_err e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') " + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') " + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'      \r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd') \r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.NOTDEAL_CNT = b.NOTDEAL_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,NOTDEAL_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.NOTDEAL_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]etcboutlist_err站上传落地省中心ect出口待治理交易数据(条):" + count);	
    }
    
    /**
     * etc出口流水拦截量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void monitorMainEtcboutlistIntecptcn(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'1' as STATIS_TYPE, count(1) INTECPT_CNT \r\n" + 
    			"      from etcboutlist_tmp e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')  " + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  " + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss') " + 
    			"       and e.signstatus = 1 \r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'      \r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd') \r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.INTECPT_CNT = b.INTECPT_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,INTECPT_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.INTECPT_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]站上传落地省中心ect出口拦截量交易数据(条):" + count);	
    }
    
    
    /**
     * etc出口流水无需治理量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void monitorMainEtcboutlistNodoCnt(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a  \r\n" + 
    			"            using ( \r\n" + 
    			"               select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'1' as STATIS_TYPE, count(1) NO_DO_CNT \r\n" + 
    			"                from tb_charge_etcboutlist e \r\n" + 
    			"               where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')  \r\n" + 
    			"                 and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" + 
    			"                 and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss') \r\n" + 
    			"                 and e.signstatus = 1 \r\n" + 
    			"                 and e.vehclass not in (14,9,39)  \r\n" + 
    			"                 and e.elepaymethod not in(7,8,107,108)  \r\n" + 
    			"                 and e.vehplate not like '闯关%'   \r\n" + 
    			"                 and haddeal=4 \r\n" + 
    			"                 group by  to_char(e.optime,'yyyy-mm-dd')  \r\n" + 
    			"            )  b \r\n" + 
    			"            on (a.statis_date=b.statis_date and a.statis_type=b.statis_type) \r\n" + 
    			"            WHEN MATCHED THEN \r\n" + 
    			"              update  set a.NO_DO_CNT = b.NO_DO_CNT,UPDATE_TIME=sysdate \r\n" + 
    			"            WHEN not MATCHED THEN \r\n" + 
    			"              insert (STATIS_DATE,STATIS_TYPE,NO_DO_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME) \r\n" + 
    			"              values (b.STATIS_DATE,b.STATIS_TYPE,b.NO_DO_CNT,b.STATIS_TYPE,sysdate,sysdate)   ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]站上传落地省中心ect出口无需治理量交易数据(条):" + count);	   	
    }
    
    /**
     * 统计站上传落地省中心出口其它交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainBoutlistAll(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"   using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'2' as STATIS_TYPE, count(1) STATION_TOTALCNT\r\n" + 
    			"      from boutlist_all e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_TOTALCNT = b.STATION_TOTALCNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_TOTALCNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_TOTALCNT,b.STATIS_TYPE,sysdate,sysdate)  ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]boutlist_all站上传落地省中心出口其它交易量数据(条):" + count);	
    }
    
    
    /**
     * 统计站上传落地省中心mtc出口其它成功交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainBoutlistAllSucc(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'2' as STATIS_TYPE, count(1) STATION_SEND_CNT\r\n" + 
    			"      from boutlist e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') " + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') " + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'      \r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd') \r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_SEND_CNT = b.STATION_SEND_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_SEND_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_SEND_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]boutlist站上传落地省中心mtc出口其它成功交易量数据(条):" + count);	
    }
    
    
    /**
     * 统计站上传落地省中心mtc出口其它待治理交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainBoutlistAllNotdeal(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'2' as STATIS_TYPE, count(1) NOTDEAL_CNT\r\n" + 
    			"      from boutlist_err e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss') " + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') " + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'      \r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd') \r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.NOTDEAL_CNT = b.NOTDEAL_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,NOTDEAL_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.NOTDEAL_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]boutlist_err站上传落地省中心mtc出口其它待治理交易量数据(条):" + count);	
    }
    
    /**
     * mtc出口流水拦截量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void monitorMainMtcboutlistIntecptcn(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'2' as STATIS_TYPE, count(1) INTECPT_CNT \r\n" + 
    			"      from boutlist_tmp e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')  " + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  " + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss') " + 
    			"       and e.signstatus = 1 \r\n" + 
    			"       and e.vehclass not in (14,9,39) \r\n" + 
    			"       and e.elepaymethod not in(7,8,107,108) \r\n" + 
    			"       and e.vehplate not like '闯关%'      \r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd') \r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.INTECPT_CNT = b.INTECPT_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,INTECPT_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.INTECPT_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]站上传落地省中心mtc出口拦截量交易数据(条):" + count);	
    }
    
    
    /**
     * mtc出口流水无需治理量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void monitorMainMtcboutlistNodoCnt(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_MAIN a  \r\n" + 
    			"            using ( \r\n" + 
    			"               select to_char(e.optime,'yyyy-mm-dd')  as statis_date,'2' as STATIS_TYPE, count(1) NO_DO_CNT \r\n" + 
    			"                from tb_charge_boutlist e \r\n" + 
    			"               where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')  \r\n" + 
    			"                 and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" + 
    			"                 and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss') \r\n" + 
    			"                 and e.signstatus = 1 \r\n" + 
    			"                 and e.vehclass not in (14,9,39)  \r\n" + 
    			"                 and e.elepaymethod not in(7,8,107,108)  \r\n" + 
    			"                 and e.vehplate not like '闯关%'   \r\n" + 
    			"                 and haddeal=4 \r\n" + 
    			"                 group by  to_char(e.optime,'yyyy-mm-dd')  \r\n" + 
    			"            )  b \r\n" + 
    			"            on (a.statis_date=b.statis_date and a.statis_type=b.statis_type) \r\n" + 
    			"            WHEN MATCHED THEN \r\n" + 
    			"              update  set a.NO_DO_CNT = b.NO_DO_CNT,UPDATE_TIME=sysdate \r\n" + 
    			"            WHEN not MATCHED THEN \r\n" + 
    			"              insert (STATIS_DATE,STATIS_TYPE,NO_DO_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME) \r\n" + 
    			"              values (b.STATIS_DATE,b.STATIS_TYPE,b.NO_DO_CNT,b.STATIS_TYPE,sysdate,sysdate)   ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]站上传落地省中心mtc出口无需治理量交易数据(条):" + count);	   	
    }
    
    
    /**
     * 统计站上传落地省中心入口ETC交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainEtcBinlistAll(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'4' as STATIS_TYPE, count(1) STATION_TOTALCNT\r\n" + 
    			"      from ETCbinlist_all e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39)      \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_TOTALCNT = b.STATION_TOTALCNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_TOTALCNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_TOTALCNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]ETCbinlist_all站上传落地省中心站上传落地省中心入口ETC交易量数据(条):" + count);	
    }
    
    
    /**
     * 统计站上传落地省中心入口ETC交易量成功
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainEtcBinlistAllSucc(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'4' as STATIS_TYPE, count(1) STATION_SEND_CNT\r\n" + 
    			"      from ETCbinlist e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39)      \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_SEND_CNT = b.STATION_SEND_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_SEND_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_SEND_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]ETCbinlist站上传落地省中心站上传落地省中心入口ETC成功交易量数据(条):" + count);	
    }
    
    
    /**
     * 统计站上传落地省中心入口ETC待治理交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainEtcBinlistAllNotdeal(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'4' as STATIS_TYPE, count(1) NOTDEAL_CNT\r\n" + 
    			"      from ETCbinlist_err e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39)      \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.NOTDEAL_CNT = b.NOTDEAL_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,NOTDEAL_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.NOTDEAL_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]ETCbinlist_err站上传落地省中心站上传落地省中心入口ETC待治理交易量数据(条):" + count);	
    }
    
    /**
     * 统计站上传落地省中心入口其它交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainBinlistAll(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'5' as STATIS_TYPE, count(1) STATION_TOTALCNT\r\n" + 
    			"      from binlist_all e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39)      \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_TOTALCNT = b.STATION_TOTALCNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_TOTALCNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_TOTALCNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]binlist_all站上传落地省中心入口其它交易量数据(条):" + count);	
    }
    
    /**
     * 统计站上传落地省中心mtc入口其它成功交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainBinlistAllSucc(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'5' as STATIS_TYPE, count(1) STATION_SEND_CNT\r\n" + 
    			"      from binlist e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39)      \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.STATION_SEND_CNT = b.STATION_SEND_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,STATION_SEND_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.STATION_SEND_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]binlist站上传落地省中心mtc入口其它成功交易量数据(条):" + count);	
    }
    
    /**
     * 统计站上传落地省中心mtc入口其它待治理交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void monitorMainBinlistAllNotdeal(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_MAIN a \r\n" + 
    			"  using (\r\n" + 
    			"     select to_char(e.optime,'yyyy-mm-dd') as statis_date,'5' as STATIS_TYPE, count(1) NOTDEAL_CNT\r\n" + 
    			"      from binlist_err e\r\n" + 
    			"     where inserttabletime >= to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss')\r\n" + 
    			"       and optime>=TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') \r\n" + 
    			"       and optime <to_date('"+endTime+"','yyyy-MM-dd hh24:mi:ss')\r\n" + 
    			"       and e.signstatus = 1\r\n" + 
    			"       and e.vehclass not in (14,9,39)      \r\n" + 
    			"       and e.vehplate not like '闯关%'\r\n" + 
    			"       group by  to_char(e.optime,'yyyy-mm-dd')\r\n" + 
    			"  )  b\r\n" + 
    			"  on (a.statis_date=b.statis_date and a.statis_type=b.statis_type)\r\n" + 
    			"  WHEN MATCHED THEN\r\n" + 
    			"    update  set a.NOTDEAL_CNT = b.NOTDEAL_CNT,UPDATE_TIME=sysdate\r\n" + 
    			"  WHEN not MATCHED THEN\r\n" + 
    			"    insert (STATIS_DATE,STATIS_TYPE,NOTDEAL_CNT,ORDER_NO,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"    values (b.STATIS_DATE,b.STATIS_TYPE,b.NOTDEAL_CNT,b.STATIS_TYPE,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通]binlist_err站上传落地省中心mtc入口其它待治理交易量数据(条):" + count);	
    }
    
   
   
}
